<?php

namespace App\Model;

use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class FinanceWealthStream extends BaseModel
{
    protected $table = 'finance_wealth_stream';
    protected $union_table = 'union';//工会表
    protected $union_user_table = 'union_user';//公会成员表
    protected $union_room_table = 'union_room';//公会房间表
    public $timestamps = false;
    protected $primaryKey = 'stream_id';
    private $fields = ['from_user_id','wealth_value','change_time','wealth_type','user_id','wealth_balance','room_id', 'gift_stream_id'];

    public function getRoomWaterList(Request $request, $room_id){
        $limit = $request->input('limit',10);
        $page = $request->input('page',1);
        $dateType = $request->input('date_type','month');
        $dateTimeArray = $request->input('value');

        $where = $begin = $end = $format = '';
        if($dateType === 'month'){
            if($dateTimeArray[0]){
                $begin = date('Y-m',strtotime($dateTimeArray[0]));
            }
            if($dateTimeArray[1]){
                $end = date('Y-m',strtotime($dateTimeArray[1]));
            }
            $format = '%Y-%m';
        } elseif ($dateType === 'year'){
            if($dateTimeArray[0]){
                $begin = date('Y',strtotime($dateTimeArray[0]));
            }
            if($dateTimeArray[1]){
                $end = date('Y',strtotime($dateTimeArray[1]));
            }
            $format = '%Y';
        }elseif ($dateType === 'week'){
            if($dateTimeArray){
                $begin = date('Y-m-d',strtotime($dateTimeArray[0]));
            }
            if($dateTimeArray){
                $end = date('Y-m-d',strtotime($dateTimeArray[1]));
            }


            $format = '%Y-%m-%d';
        }

        if($begin && !$end){
            $where .= " AND FROM_UNIXTIME(a.change_timestamp, '{$format}')>='$begin'";
        }

        if(!$begin && $end){
            $where .= " AND FROM_UNIXTIME(a.change_timestamp, '{$format}')<='$end'";
        }
        if($begin && $end){
            $where .= " AND FROM_UNIXTIME(a.change_timestamp, '{$format}')>='$begin' AND FROM_UNIXTIME(a.change_timestamp, '{$format}')<='{$end}'";
        }

        $sql = "SELECT FROM_UNIXTIME(a.change_timestamp, '{$format}') as created_at,SUM(a.room_wealth_value) as room_wealth_value,a.room_id,b.room_uid,b.name FROM zb_finance_wealth_stream AS a LEFT JOIN zb_room AS b ON a.room_id=b.room_id WHERE a.room_id={$room_id} $where GROUP BY created_at,a.room_id";
        $data = DB::connection($this->connection)->select($sql);
        $total = count($data);
        $data = collect($data)->forPage($page,$limit)->values()->toArray();
        return ['total' => $total, 'data' => $data];
    }


    public function getRoomWealthWater(Request $request, $room_id)
    {
        $limit = $request->input('limit',10);
        $page = $request->input('page',1);
        $dateType = $request->input('date_type','month');
        $dateTimeArray = $request->input('value');
        
        $where = $begin = $end = $format = '';
        
        if($dateTimeArray[0]){
            $begin = date('Y-m-d',strtotime($dateTimeArray[0]));
        }
        if($dateTimeArray[1]){
            $end = date('Y-m-d',strtotime($dateTimeArray[1]));
        }
        $format = '%Y-%m-%d';
        

        if($begin && !$end){
            $where .= " AND FROM_UNIXTIME(a.create_timestamp, '{$format}')>='$begin'";
        }

        if(!$begin && $end){
            $where .= " AND FROM_UNIXTIME(a.create_timestamp, '{$format}')<='$end'";
        }
        if($begin && $end){
            $where .= " AND FROM_UNIXTIME(a.create_timestamp, '{$format}')>='$begin' AND FROM_UNIXTIME(a.create_timestamp, '{$format}')<='{$end}'";
        }

        $sql = "SELECT FROM_UNIXTIME(a.create_timestamp, '{$format}') as created_at,SUM(a.wealth_value)
            as room_wealth_value,a.room_id,b.room_uid,b.name FROM zb_finance_room_wealth_stream
            AS a LEFT JOIN zb_room AS b ON a.room_id=b.room_id WHERE a.room_id={$room_id} $where GROUP BY created_at,a.room_id";
        $data = DB::connection($this->connection)->select($sql);
        $total = count($data);
        $data = collect($data)->forPage($page,$limit)->values()->toArray();
        $count_value = 0;
        foreach($data as $v){
            $count_value += $v->room_wealth_value;
        }
        if($data){
            $count['created_at'] =  'N/A';
            if($begin && $end) $count['created_at'] = $begin .'至'. $end;
            $count['room_uid'] = '房间统计';
            $count['name'] = 'N/A';
            $count['room_wealth_value'] = $count_value;
            $data[] = json_decode(json_encode($count));
        }
        return ['total' => $total, 'data' => $data];
    }

    public function getUnionRoomWealthWater(Request $request, $room_idStr){
        $limit = $request->input('limit',10);
        $page = $request->input('page',1);
        $dateType = $request->input('date_type','month');
        $dateTimeArray = $request->input('value');

        $where = $begin = $end = $format = '';
       
        if($dateTimeArray[0]){
            $begin = date('Y-m-d',strtotime($dateTimeArray[0]));
        }
        if($dateTimeArray[1]){
            $end = date('Y-m-d',strtotime($dateTimeArray[1]));
        }
        $format = '%Y-%m-%d';

        if($begin && !$end){
            $where .= " AND FROM_UNIXTIME(a.create_timestamp, '{$format}')>='$begin'";
        }

        if(!$begin && $end){
            $where .= " AND FROM_UNIXTIME(a.create_timestamp, '{$format}')<='$end'";
        }
        if($begin && $end){
            $where .= " AND FROM_UNIXTIME(a.create_timestamp, '{$format}')>='$begin' AND FROM_UNIXTIME(a.create_timestamp, '{$format}')<='{$end}'";
        }

        $sql = "SELECT FROM_UNIXTIME(a.create_timestamp, '{$format}') as created_at,SUM(a.wealth_value)
            as room_wealth_value,a.room_id,b.room_uid,b.name FROM zb_finance_room_wealth_stream
            AS a LEFT JOIN zb_room AS b ON a.room_id=b.room_id WHERE a.room_id in ({$room_idStr}) $where GROUP BY created_at,a.room_id";
        $data = DB::connection($this->connection)->select($sql);
        $total = count($data);
        $data = collect($data)->forPage($page,$limit)->values()->toArray();
        $count_value = 0;
        foreach($data as $v){
            $count_value += $v->room_wealth_value;
        }
        if($data){
            $count['created_at'] =  'N/A';
            if($begin && $end) $count['created_at'] = $begin .'至'. $end;
            $count['room_uid'] = '房间统计';
            $count['name'] = 'N/A';
            $count['room_wealth_value'] = $count_value;
            $data[] = json_decode(json_encode($count));
        }
        return ['total' => $total, 'data' => $data];
    }
    //获取房间魅力榜
    public function getRoomCharm(Request $request, $room_id)
    {
        $limit = $request->input('limit',10);
        $page = $request->input('page',1);
        $dateType = $request->input('date_type','month');
        $dateTimeArray = $request->input('value');
        $where = $begin = $end = $format = '';
        
        if( isset($dateTimeArray[0]) && $dateTimeArray[0]){
            $begin = date('Y-m-d',strtotime($dateTimeArray[0]));
        }
        if(isset($dateTimeArray[1]) && $dateTimeArray[1]){
            $end = date('Y-m-d',strtotime($dateTimeArray[1]));
        }
        $format = '%Y-%m-%d';
        
        if($begin && !$end){
            $where .= " AND FROM_UNIXTIME(a.create_timestamp, '{$format}')>='$begin'";
        }

        if(!$begin && $end){
            $where .= " AND FROM_UNIXTIME(a.create_timestamp, '{$format}')<='$end'";
        }
        if($begin && $end){
            $where .= " AND FROM_UNIXTIME(a.create_timestamp, '{$format}')>='$begin' AND FROM_UNIXTIME(a.create_timestamp, '{$format}')<='{$end}'";
        }

        $sql = "SELECT SUM(a.wealth_value) as room_wealth_value,a.room_id,b.uuid,b.nickname,b.user_id
                 FROM zb_finance_room_wealth_stream AS a LEFT JOIN zb_user AS b
                 ON a.from_user_id=b.user_id
                 WHERE a.room_id={$room_id} $where
                 GROUP BY a.room_id,a.from_user_id
                 ORDER BY room_wealth_value desc";

        $data = DB::connection($this->connection)->select($sql);
        $total = count($data);
        $data = collect($data)->forPage($page,$limit)->values()->toArray();
        $data = objToArray($data);
        $count_value = 0;
        foreach($data as $k => $v){
            $count_value += $v['room_wealth_value'];
            //判断是主播还是用户
            $exists = DB::connection($this->connection)
                        ->table($this->union_table)
                        ->join($this->union_user_table,'union.id','=','union_user.un_id')
                        ->join($this->union_room_table,'union.id','=','union_room.un_id')
                        ->wherein('union.authentication_status',[1,2])
                        ->where('union_user.user_id',$v['user_id'])
                        ->where('union_room.room_id',$room_id)
                        ->exists();
            //$exists = DB::connection($this->connection)->table($this->union_user_table)->where('user_id',$v['user_id'])->exists();
            $data[$k]['is_anchor'] = 0;
            if($exists)
            {
                $data[$k]['is_anchor'] = 1;//1主播0用户
            }
        }
        if(empty($data)){
            $count['uuid'] =  'N/A';
            if($begin && $end) $count['created_at'] = $begin .'至'. $end;
            $count['nickname'] = 'N/A';
            $count['room_wealth_value'] = $count_value;
            $data[] = json_decode(json_encode($count));
        }
        return ['total' => $total, 'data' => $data];
    }

    public function getRoomWealth(Request $request, $room_id)
    {
        $limit = $request->input('limit',10);
        $page = $request->input('page',1);
        $dateType = $request->input('date_type','month');
        $dateTimeArray = $request->input('value');

        $where = $begin = $end = $format = '';
        
        $format = '%Y-%m-%d';

        if( isset($dateTimeArray[0]) && $dateTimeArray[0]){
            $begin = date('Y-m-d',strtotime($dateTimeArray[0]));
        }
        if(isset($dateTimeArray[1]) && $dateTimeArray[1]){
            $end = date('Y-m-d',strtotime($dateTimeArray[1]));
        }

        if($begin && !$end){
            $where .= " AND FROM_UNIXTIME(a.create_timestamp, '{$format}')>='$begin'";
        }

        if(!$begin && $end){
            $where .= " AND FROM_UNIXTIME(a.create_timestamp, '{$format}')<='$end'";
        }
        if($begin && $end){
            $where .= " AND FROM_UNIXTIME(a.create_timestamp, '{$format}')>='$begin' AND FROM_UNIXTIME(a.create_timestamp, '{$format}')<='{$end}'";
        }

        $sql = "SELECT SUM(a.wealth_value) as room_wealth_value,a.room_id,b.uuid,b.nickname,b.user_id
                 FROM zb_finance_room_wealth_stream AS a LEFT JOIN zb_user AS b
                 ON a.user_id=b.user_id
                 WHERE a.room_id={$room_id} $where
                 GROUP BY a.room_id,a.user_id
                 ORDER BY room_wealth_value desc";

        $data = DB::connection($this->connection)->select($sql);

        $total = count($data);
        $data = collect($data) -> forPage($page,$limit)->values()->toArray();
        $count_value = 0;
        $data = objToArray($data);
        foreach($data as $k=>$v){
            $count_value += $v['room_wealth_value'];
            //判断是主播还是用户
            $exists = DB::connection($this->connection)
                        ->table($this->union_table)
                        ->join($this->union_user_table,'union.id','=','union_user.un_id')
                        ->join($this->union_room_table,'union.id','=','union_room.un_id')
                        ->wherein('union.authentication_status',[1,2])
                        ->where('union_user.user_id',$v['user_id'])
                        ->where('union_room.room_id',$room_id)
                        ->exists();
            //$exists = DB::connection($this->connection)->table($this->union_user_table)->where('user_id',$v['user_id'])->exists();
            $data[$k]['is_anchor'] = 0;
            if($exists)
            {
                $data[$k]['is_anchor'] = 1;//1主播0用户
            }
        }
        if(empty($data)){
            $count['uuid'] =  'N/A';
            if($begin && $end) $count['created_at'] = $begin .'至'. $end;
            $count['nickname'] = 'N/A';
            $count['room_wealth_value'] = $count_value;
            $data[] = json_decode(json_encode($count));
        }
        return ['total' => $total, 'data' => $data];
    }


    public function getList(Request $request){
        $page = $request->get('page', 0);
        $limit = $request->get('limit', 10);
        $uuid = $request->get('uuid','');
        $nickname = $request->get('nickname','');
        $wealth_type = $request->get('type',null);
        $begin_date = $request->get('begin_date',0);
        $end_date = $request->get('end_date',0);
        $user_id = $request->get('user_id',0);

        $query = new self();
        $query = $query->whereHas('number',function ($query) use($uuid, $nickname){
            $query->select(['user_id']);
            if($uuid) $query->where('uuid',$uuid);
            if($nickname) $query->where('nickname','like',"%{$nickname}%");
        });

        if($wealth_type >= 0) {
            $query = $query->where('wealth_type',$wealth_type);
        } elseif($wealth_type == -2) {
            $query = $query->whereNotIn('wealth_type', [0,1,2]);
        }
        if($user_id) $query = $query->where('user_id',$user_id);
        if($begin_date && $end_date) $query = $query->whereBetween('change_time', [$begin_date, $end_date]);
        if($begin_date && !$end_date) $query = $query->where('change_time','>=',$begin_date);
        if(!$begin_date && $end_date) $query = $query->where('change_time','<=',$end_date);

        return $query->orderBy('stream_id','desc')
            ->with(['number:user_id,uuid,nickname','sender:user_id,uuid,nickname','room:room_id,name','giftstream:stream_id,gift_name'])
            ->paginate($limit,$this->fields,'page',$page);
    }

    /**
     * 接收者
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function number(){
        return $this->belongsTo(Number::class,'user_id','user_id');
    }

    /**
     * 发送者
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function sender(){
        return $this->belongsTo(Number::class,'from_user_id','user_id');
    }

    /**
     * 房间
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function room(){
        return $this->belongsTo(Room::class,'room_id','room_id');
    }

    /**
     * 礼物流水
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function giftstream(){
        return $this->belongsTo(GiftStream::class,'gift_stream_id','stream_id');
    }

    public function getRoomAllStream($room_type, $start_date, $end_date){
        $roomSql = "SELECT `room_id`    FROM `zb_room` WHERE business_type =1 and zb_room.certified =1 ";
        if ($room_type){
            $roomSql .= ' AND room_type = '.$room_type;
        }
        $sql ="SELECT sum(wealth_value) as all_wealth,room_id FROM `zb_finance_room_wealth_stream` WHERE `room_id` in (
".$roomSql."
)
    AND `create_timestamp` BETWEEN ".$start_date." and	".$end_date." GROUP BY room_id";
//        write_log('getRoomStream', 'getRoomAllStream',$sql);//未生成数据
        $res = DB::connection($this->connection)->select($sql);

        return $this->objToArray($res);
    }
}
